//     ____    _                        __     _      _____
//    / ___\  /_\     /\/\    /\ /\    /__\   /_\     \_   \
//    \ \    //_\\   /    \  / / \ \  / \//  //_\\     / /\/
//  /\_\ \  /  _  \ / /\/\ \ \ \_/ / / _  \ /  _  \ /\/ /_
//  \____/  \_/ \_/ \/    \/  \___/  \/ \_/ \_/ \_/ \____/
//
//	         Copyright The Only Love by 周凯东
#import "DataBase.h"

@implementation DataBase

+(DataBase *)shareInstance
{
   //通过GCD多线程 实现的单例
    static DataBase *dbManager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dbManager = [[DataBase alloc]init];
        
    });
    return  dbManager;
}

//开启数据库
-(void)openDB
{
    //在本地创建一个数据库文件 一般存放在 documents文件夹下
    //获取documents的路径
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    //拼接数据库路径(后缀名必须是: .db / .sqlite)
    NSString *dbPath = [path stringByAppendingPathComponent:@"DataBase.db"];
    //创建数据库
    //数据库打开函数 会检测路径下 是否已经存在数据库 (看dbPointer指针 是否指向了数据库) 如果不存在 则创建一个新的数据库 如果存在 直接打开原有的数据库
    //参数1 : 本地数据库路径(UTF-8转码)
    //参数2 : 数据库指针
    int result = sqlite3_open([dbPath UTF8String], &dbPointer);
//    NSLog(@"打开结果 :%d, path : %@", result, dbPath);
}
//关闭数据库
-(void)closeDB
{
    sqlite3_close(dbPointer);
}
//创建一个表
-(void)createTable
{
    //1.创建一条sql语句
    //creat table 创建表
    //if not exists 如果不存在
    //primary key 主键(用来作为每一条信息的唯一标识)
    //autoincrement 自增
    NSString *createSQL = @"create table if not exists User (name text, image text , URL text , num integer primary key autoincrement)";
    
    //2.执行语句
    //参数1 :数据库指针
    //参数2 :sql语句
   int result = sqlite3_exec(dbPointer, createSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self jubgeRuselt:result type:@"chuangjianbiao"];
    
    
}
-(void)createItemsTable
{
    //1.创建一条sql语句
    //creat table 创建表
    //if not exists 如果不存在
    //primary key 主键(用来作为每一条信息的唯一标识)
    //autoincrement 自增
    NSString *createSQL = @"create table if not exists Items (name text, image text , URL text , num integer primary key autoincrement)";
    
    //2.执行语句
    //参数1 :数据库指针
    //参数2 :sql语句
    int result = sqlite3_exec(dbPointer, createSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self jubgeRuselt:result type:@"chuangjianbiao"];
}

//添加
-(void)insertInfoWithUser:(UserModel *)user
{
    //1.创建sql语句
    NSString *insertSQL = [NSString stringWithFormat:@"insert into User (name, image, URL) values('%@','%@','%@')",user.name , user.image, user.URL];
    
    //2.执行
    int result = sqlite3_exec(dbPointer, insertSQL.UTF8String, NULL, NULL, NULL);
    
    //3.判断
    [self jubgeRuselt:result type:@"charu"];
}
-(void)insertItemsInfoWithUser:(UserModel *)user
{
    //1.创建sql语句
    NSString *insertSQL = [NSString stringWithFormat:@"insert into Items (name, image, URL) values('%@','%@','%@')",user.name , user.image, user.URL];
    
    //2.执行
    int result = sqlite3_exec(dbPointer, insertSQL.UTF8String, NULL, NULL, NULL);
    
    //3.判断
    [self jubgeRuselt:result type:@"charu"];
}

//判断方法
-(void)jubgeRuselt:(int)r type:(NSString *)type
{
//    if(r == SQLITE_OK) {
//        NSLog(@"%@操作成功", type);
//    } else {
//        NSLog(@"%@操作失败,原因:%d", type, r);
//    }
}
//删除数据
-(void)deleteInfoWithName:(NSString *)name
{
    //1.sql语句
    //delete form 从哪个表删除
    //where 删除条件
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from User where name = '%@'", name ];
    //2.执行
    int result = sqlite3_exec(dbPointer, deleteSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self jubgeRuselt:result type:@"shanchu"];
}
-(void)deleteItemsInfoWithName:(NSString *)name
{
    //1.sql语句
    //delete form 从哪个表删除
    //where 删除条件
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from Items where name = '%@'", name ];
    //2.执行
    int result = sqlite3_exec(dbPointer, deleteSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self jubgeRuselt:result type:@"shanchu"];
}
//修改数据
-(void)updateInfoWithUser:(UserModel *)user num:(NSInteger)num
{
    //1.sql语句
    //update 更新
    //set    设置
    //where  约束
    NSString *updateSQL = [NSString stringWithFormat:@"update User set name = '%@' , image = '%@' where  URL = '%@'", user.name, user.image, user.URL];
    //2.执行
    int result = sqlite3_exec(dbPointer, updateSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self jubgeRuselt:result type:@"gengxin"];
}
//查询数据
-(NSMutableArray *)selectInfo
{
    //查询操作逻辑
    //1.从本地所有数据所有信息中 遍历获取每条信息
    //2.把每条信息 转化为model对象
    //3.把model添加在数组里 进行返回
    
    
    //1.sql语句
    //select * 查询所有数据
    NSString *selcetSQL = @"select * from User";
    //2.创建数据库替身
    sqlite3_stmt *stmt = nil;
    //3.准备sql语句
    //prepare_v2函数 把数据库对象(dbPointer/SQL语句/数据库替身 关联在一起)
    int result = sqlite3_prepare_v2(dbPointer, selcetSQL.UTF8String, -1, &stmt, nil);
    //4.创建用于返回数据的 数组
    NSMutableArray *arr = [NSMutableArray array];
    //5.判断查询准备是否成功
    if(result == SQLITE_OK) {
//        NSLog(@"查询准备成功");
    //6.开始遍历每一行信息
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当数据库替身指向数据 符合查询条件 在while中返回
            //逐行 获取每一列信息
            //列数 从0开始
            const unsigned char *name = sqlite3_column_text(stmt, 0);
            const unsigned char *image = sqlite3_column_text(stmt, 1);
            
            const unsigned char *URL = sqlite3_column_text(stmt, 2);
            //把获取到的数据库信息 保存在model
            UserModel *model = [[UserModel alloc]init];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.image = [NSString stringWithUTF8String:(const char *)image];
            model.URL = [NSString stringWithUTF8String:(const char *)URL];
  
            //添加到数组中
            [arr addObject:model];
            [model release];
            
            
            
        }
    } else {
//        NSLog(@"查询准备失败, 原因:%d",result);
    }
    
    
    return arr;
    
}
-(NSMutableArray *)selectItemsInfo
{
    //查询操作逻辑
    //1.从本地所有数据所有信息中 遍历获取每条信息
    //2.把每条信息 转化为model对象
    //3.把model添加在数组里 进行返回
    
    
    //1.sql语句
    //select * 查询所有数据
    NSString *selcetSQL = @"select * from Items";
    //2.创建数据库替身
    sqlite3_stmt *stmt = nil;
    //3.准备sql语句
    //prepare_v2函数 把数据库对象(dbPointer/SQL语句/数据库替身 关联在一起)
    int result = sqlite3_prepare_v2(dbPointer, selcetSQL.UTF8String, -1, &stmt, nil);
    //4.创建用于返回数据的 数组
    NSMutableArray *arr = [NSMutableArray array];
    //5.判断查询准备是否成功
    if(result == SQLITE_OK) {
//        NSLog(@"查询准备成功");
        //6.开始遍历每一行信息
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当数据库替身指向数据 符合查询条件 在while中返回
            //逐行 获取每一列信息
            //列数 从0开始
            const unsigned char *name = sqlite3_column_text(stmt, 0);
            const unsigned char *image = sqlite3_column_text(stmt, 1);
            
            const unsigned char *URL = sqlite3_column_text(stmt, 2);
            //把获取到的数据库信息 保存在model
            UserModel *model = [[UserModel alloc]init];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.image = [NSString stringWithUTF8String:(const char *)image];
            model.URL = [NSString stringWithUTF8String:(const char *)URL];
            
            //添加到数组中
            [arr addObject:model];
            [model release];
            
            
            
        }
    } else {
//        NSLog(@"查询准备失败, 原因:%d",result);
    }
    
    
    return arr;
}

//删除biao
-(void)dropTable
{
    //1.sql语句
    NSString *dropSQL = @"drop table User";
    //2.执行
    int result = sqlite3_exec(dbPointer, dropSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self jubgeRuselt:result type:@"删除表"];
}
-(void)dropItemsTable
{
    //1.sql语句
    NSString *dropSQL = @"drop table Items";
    //2.执行
    int result = sqlite3_exec(dbPointer, dropSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self jubgeRuselt:result type:@"删除表"];
}

//-(void)dropItemsTable
//{
//    //1.sql语句
//    NSString *dropSQL = @"drop table Items";
//    //2.执行
//    int result = sqlite3_exec(dbPointer, dropSQL.UTF8String, NULL, NULL, NULL);
//    //3.判断
//    [self jubgeRuselt:result type:@"删除表"];
//}

@end
